<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
<meta name="viewport"
      content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">

    <meta name="author" content="Janmas">



    <meta name="description" content="Janmas！一个又菜又爱玩的程序猿。不停的在立Flag，又一直在凭实力打着自己的脸。">


    <meta name="keywords" content="Janmas,Blog,墨鱼">


<title>
    索引原理 |
    
    Janmas</title>



    <link rel="icon" href="/favicon.ico">



    <!-- stylesheets list from _config.yml -->
    
        <link rel="stylesheet" href="/css/style.css">
    



    <!-- scripts list from _config.yml -->
    
        <script src="/js/script.js"></script>
    
        <script src="/js/tocbot.min.js"></script>
    



    
    
        
    



    <script src='https://unpkg.com/mermaid@7.1.2/dist/mermaid.min.js'></script>
    <script>
        if (window.mermaid) {
            mermaid.initialize({theme: 'forest'});
        }
    </script>


<meta name="generator" content="Hexo 5.4.1"></head>

<body>
    <script>
        // this function is used to check current theme before page loaded.
        (() => {
            const currentTheme = window.localStorage && window.localStorage.getItem('theme') || '';
            const isDark = currentTheme === 'dark';
            const pagebody = document.getElementsByTagName('body')[0]
            if (isDark) {
                pagebody.classList.add('dark-theme');
                // mobile
                document.getElementById("mobile-toggle-theme").innerText = "· Dark"
            } else {
                pagebody.classList.remove('dark-theme');
                // mobile
                document.getElementById("mobile-toggle-theme").innerText = "· Light"
            }
        })();
    </script>

    <div class="wrapper">
        <header>
    <nav class="navbar">
        <div class="container">
            <div class="navbar-header header-logo"><a href="/">Janmas&#39;s Blog</a></div>
            <div class="menu navbar-right">
                
                    <a class="menu-item" href="/archives">Posts</a>
                
                    <a class="menu-item" href="/category">Categories</a>
                
                    <a class="menu-item" href="/tag">Tags</a>
                
                <input id="switch_default" type="checkbox" class="switch_default">
                <label for="switch_default" class="toggleBtn"></label>
            </div>
        </div>
    </nav>

    
    <nav class="navbar-mobile" id="nav-mobile">
        <div class="container">
            <div class="navbar-header">
                <div>
                    <a href="/">Janmas&#39;s Blog</a><a id="mobile-toggle-theme">·&nbsp;Light</a>
                </div>
                <div class="menu-toggle" onclick="mobileBtn()">&#9776; Menu</div>
            </div>
            <div class="menu" id="mobile-menu">
                
                    <a class="menu-item" href="/archives">Posts</a>
                
                    <a class="menu-item" href="/category">Categories</a>
                
                    <a class="menu-item" href="/tag">Tags</a>
                
            </div>
        </div>
    </nav>

</header>
<script>
    var mobileBtn = function f() {
        var toggleMenu = document.getElementsByClassName("menu-toggle")[0];
        var mobileMenu = document.getElementById("mobile-menu");
        if(toggleMenu.classList.contains("active")){
           toggleMenu.classList.remove("active")
            mobileMenu.classList.remove("active")
        }else{
            toggleMenu.classList.add("active")
            mobileMenu.classList.add("active")
        }
    }
</script>
            <div class="main">
                <div class="container">
    
    
        <div class="post-toc">
    <div class="tocbot-list">
    </div>
    <div class="tocbot-list-menu">
        <a class="tocbot-toc-expand" onclick="expand_toc()">Expand all</a>
        <a onclick="go_top()">Back to top</a>
        <a onclick="go_bottom()">Go to bottom</a>
    </div>
</div>

<script>
    document.ready(
        function () {
            tocbot.init({
                tocSelector: '.tocbot-list',
                contentSelector: '.post-content',
                headingSelector: 'h1, h2, h3, h4, h5',
                collapseDepth: 1,
                orderedList: false,
                scrollSmooth: true,
            })
        }
    )

    function expand_toc() {
        var b = document.querySelector(".tocbot-toc-expand");
        tocbot.init({
            tocSelector: '.tocbot-list',
            contentSelector: '.post-content',
            headingSelector: 'h1, h2, h3, h4, h5',
            collapseDepth: 6,
            orderedList: false,
            scrollSmooth: true,
        });
        b.setAttribute("onclick", "collapse_toc()");
        b.innerHTML = "Collapse all"
    }

    function collapse_toc() {
        var b = document.querySelector(".tocbot-toc-expand");
        tocbot.init({
            tocSelector: '.tocbot-list',
            contentSelector: '.post-content',
            headingSelector: 'h1, h2, h3, h4, h5',
            collapseDepth: 1,
            orderedList: false,
            scrollSmooth: true,
        });
        b.setAttribute("onclick", "expand_toc()");
        b.innerHTML = "Expand all"
    }

    function go_top() {
        window.scrollTo(0, 0);
    }

    function go_bottom() {
        window.scrollTo(0, document.body.scrollHeight);
    }

</script>
    

    
    <article class="post-wrap">
        <header class="post-header">
            <h1 class="post-title">索引原理</h1>
            
                <div class="post-meta">
                    
                        Author: <a itemprop="author" rel="author" href="/">Janmas</a>
                    

                    
                        <span class="post-time">
                        Date: <a href="#">March 17, 2022&nbsp;&nbsp;9:48:26</a>
                        </span>
                    
                    
                </div>
            
        </header>

        <div class="post-content">
            <p>索引就是帮助数据库高效查找数据的存储结构，存储再磁盘中，需要消耗磁盘IO</p>
<h2 id="存储引擎"><a href="#存储引擎" class="headerlink" title="存储引擎"></a>存储引擎</h2><ul>
<li><p>myisam 支持表锁，索引和数据分开存储适合跨服务器迁移</p>
</li>
<li><p>innodb 支持行锁，索引和数据存储再一个文件</p>
</li>
</ul>
<h3 id="索引类型"><a href="#索引类型" class="headerlink" title="索引类型"></a>索引类型</h3><h4 id="hash索引"><a href="#hash索引" class="headerlink" title="hash索引"></a>hash索引</h4><ul>
<li>适合精确查询且效率高</li>
<li>无法排序、不适合范围查询</li>
<li>hash冲突的情况下需要遍历链表（php数组的实现原理、redis zset 的实现原理类似）</li>
</ul>
<h4 id="b-tree-和b-tree的去区别"><a href="#b-tree-和b-tree的去区别" class="headerlink" title="b-tree 和b+tree的去区别"></a>b-tree 和b+tree的去区别</h4><ul>
<li><p>b+tree 的数据全部存储在叶子节点，内部节点只存key,一次磁盘IO能获取到更多的节点</p>
</li>
<li><p>b-tree 的内部节点和叶子节点都存储key和数据，查找数据不需要找到叶子节点，内部节点可以直接返回数据</p>
</li>
<li><p>b+tree 增加了叶子节点到相邻节点的指针，方便返回查询遍历</p>
</li>
</ul>
<h4 id="聚簇索引和非聚簇索引的区别"><a href="#聚簇索引和非聚簇索引的区别" class="headerlink" title="聚簇索引和非聚簇索引的区别"></a>聚簇索引和非聚簇索引的区别</h4><ul>
<li><p>聚簇索引  :索引和数据存储在一个节点</p>
</li>
<li><p>非聚簇索引:索引和数据分开存储，通过索引找到数据实际存储的地址</p>
</li>
</ul>
<h4 id="详解聚簇索引和非聚簇索引"><a href="#详解聚簇索引和非聚簇索引" class="headerlink" title="详解聚簇索引和非聚簇索引"></a>详解聚簇索引和非聚簇索引</h4><ul>
<li><p>innodb 使用的聚簇索引,且默认主键索引为聚簇索引（没有主键索引的时候，选择一个非空索引，还没有则隐式的主键索引），辅助索引指向聚簇索引位置，然后在找到实际存储地址</p>
</li>
<li><p>myisam 使用非聚簇索引，所有的索引都只需要查询一次就能找到数据</p>
</li>
</ul>
<h4 id="聚簇索引的优势和略势"><a href="#聚簇索引的优势和略势" class="headerlink" title="聚簇索引的优势和略势"></a>聚簇索引的优势和略势</h4><ul>
<li><p>索引和数据在一起，同一页的数据会被缓存到（buffer）内存中，所以查看同一页数据的时候只需要从内存中取出，</p>
</li>
<li><p>数据更新之后之只需要维护主键索引即可，辅助索引不受影响</p>
</li>
<li><p>辅助索引存的是主键索引的值，占用更多的物理空间。所以会受到影响</p>
</li>
<li><p>使用随机的UUID，数据分布不均匀，导致聚簇索引可能扫全表，降低效率，所以尽量使用自增主键id </p>
</li>
</ul>
<h2 id="索引原理补充（2022-03-11）"><a href="#索引原理补充（2022-03-11）" class="headerlink" title="索引原理补充（2022-03-11）"></a>索引原理补充（2022-03-11）</h2><h3 id="索引原理"><a href="#索引原理" class="headerlink" title="索引原理"></a>索引原理</h3><h4 id="磁盘IO："><a href="#磁盘IO：" class="headerlink" title="磁盘IO："></a>磁盘IO：</h4><ul>
<li>寻道时间：指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下</li>
<li>旋转延迟：指的是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms</li>
<li>传输时间：指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。</li>
<li>磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分。访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右</li>
</ul>
<h4 id="预读"><a href="#预读" class="headerlink" title="预读"></a>预读</h4><ul>
<li>考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)</li>
<li>具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。<h4 id="B-tree"><a href="#B-tree" class="headerlink" title="B+tree"></a>B+tree</h4></li>
<li>一次IO将一页的数据加载到内存中</li>
<li>为什么索引字段尽量小并且数据只存在叶子节点：字节越少，每次IO请求扫描的节点就越多<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低</span><br></pre></td></tr></table></figure></li>
<li>最左前缀特性：b+数是按照从左到右的顺序来建立搜索树的</li>
</ul>
<h4 id="索引创建原则"><a href="#索引创建原则" class="headerlink" title="索引创建原则"></a>索引创建原则</h4><ul>
<li>最左前缀原则：mysql会一直向右匹配直到遇到范围查询(&gt;、&lt;、between、like)就停止匹配。联合索引（A,B,C） = (A) + (A,B) +(A,B,C) （更新于2022-03-02：之前错误写法： (A) + (B) + (C) + (A,B) +(A,B,C)）</li>
<li>=和in可以乱序,mysql查询优化器会自动识别成需要使用索引的类型</li>
<li>尽量选择区分度高的列作为索引</li>
<li>索引列不能参与计算</li>
<li>尽量的扩展索引，不要新建索引，入过已经存在A索引 需要加一个新的索引，建议修改原来的索引为（A,B）联合索引  </li>
</ul>
<blockquote>
<p>OR 是否能走索引及原理<br>GROUP BY 是否能走索引及原理<br>ORDER BY 能否走索引及原理</p>
</blockquote>
<p>参考：</p>
<p><a href="https://cloud.tencent.com/developer/article/1541265">聚簇索引与非聚簇索引（也叫二级索引）–最清楚的一篇讲解 - 云+社区 - 腾讯云 (tencent.com)</a></p>
<p><a href="https://tech.meituan.com/2014/06/30/mysql-index.html">MySQL索引原理及慢查询优化 - 美团技术团队 (meituan.com)</a></p>
<p>————————————————<br>原文作者：snakelis<br>转自链接：<a href="https://learnku.com/laravel/t/65632">https://learnku.com/laravel/t/65632</a><br>版权声明：著作权归作者所有。商业转载请联系作者获得授权，非商业转载请保留以上作者信息和原文链接。</p>

        </div>

        
            <section class="post-copyright">
                
                
                
                

            </section>
        
        <section class="post-tags">
            <div>
                <span>Tag(s):</span>
                <span class="tag">
                    
                    
                        <a href="/tags/MySQL/"># MySQL</a>
                    
                        <a href="/tags/%E7%B4%A2%E5%BC%95/"># 索引</a>
                    
                        
                </span>
            </div>
            <div>
                <a href="javascript:window.history.back();">back</a>
                <span>· </span>
                <a href="/">home</a>
            </div>
        </section>
        <section class="post-nav">
            
            
            <a class="next" rel="next" href="/2022/01/25/vim%E9%94%AE%E4%BD%8D%E6%95%B4%E7%90%86/">vim键位整理</a>
            
        </section>


    </article>
</div>

            </div>
            <footer id="footer" class="footer">
    <div class="copyright">
        <span>© Janmas | Powered by <a href="https://hexo.io" target="_blank">Hexo</a> & <a href="https://github.com/Siricee/hexo-theme-Chic" target="_blank">Chic</a></span>
    </div>
</footer>



    </div>
</body>

</html>